﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DADOS.auxiliares
{
     public class metRelatorio
    {
        DB_PROAD db = new DB_PROAD();

        private SqlConnection _conexao = new SqlConnection(ConfigurationManager.ConnectionStrings["DB_PROAD_SQL"].ConnectionString);

        public List<TB_ATENCAO_SUS> ListarAtencao()
        {
            try
            {
                db = new DB_PROAD();
              var  objAtencao = db.TB_ATENCAO_SUS.ToList();
              return objAtencao;
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                db.Connection.Close();
            }
           
        }

        public DataSet ListarUsuario(int intEquipe, int intAtencao)
        {
            DataSet ds = new DataSet();

            string Query = "SELECT USUA.NO_NOME,";
		                Query += "   CASE USUA.NR_SEXO WHEN '2'";
		                Query += "       THEN 'Feminino'";
                        Query += " ELSE 'Masculino' END AS SEXO,";
	                    Query += "      EQUIPE.NO_ES,";
                        Query += "  ATEN.NO_ATENCAO_SUS		 ";
                        Query += "  FROM TB_USUARIO AS USUA ";
                        Query += "   INNER JOIN TB_ES AS EQUIPE";
                        Query += " ON EQUIPE.CO_ES = USUA.CO_ES ";
                        Query += "   INNER JOIN TB_ATENCAO_SUS AS ATEN";
                        Query += " ON ATEN.CO_ATENCAO_SUS = EQUIPE.CO_ATENCAO_SUS";
                        Query += " WHERE 1 = 1";

            if (intEquipe > 0)
            {
                Query += " AND EQUIPE.CO_ES = " + intEquipe;
            }

            if (intAtencao > 0)
            {
                Query += " AND ATEN.CO_ATENCAO_SUS = " + intAtencao;
            }



            Query += " ORDER BY ATEN.NO_ATENCAO_SUS";

            SqlCommand cmd = new SqlCommand(Query, _conexao);

            cmd.Connection = _conexao;

            if (_conexao.State == ConnectionState.Closed)
            {
                _conexao.Open();
            }
            SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
            dataAdapter.Fill(ds);

            _conexao.Close();

            return ds;
        }


        public DataSet ListarAtmEq(int intCodUf, string dataInicio, string dataFim)
        {
            DataSet ds = new DataSet();

            string Query = "SELECT DISTINCT TOP 1 ";
	               Query += "  (SELECT COUNT(*) FROM TB_PRONTUARIO AS PRONT "; 
			       Query += "                 INNER JOIN TB_USUARIO AS USU ";
				   Query += "                     ON USU.CO_USUARIO = PRONT.CO_USUARIO ";
			       Query += "                 INNER JOIN TB_ES AS ES ";
				   Query += "                     ON ES.CO_ES = USU.CO_ES ";
			       Query += "                 INNER JOIN TB_US AS US ";
				   Query += "                     ON US.CO_US = ES.CO_US ";
                   Query += "                 WHERE 1 = 1 ";
                   if (intCodUf > 0)
                   {
                       Query += "                 AND US.CO_UF = " + intCodUf;
                   }
                   
                   if (!string.IsNullOrEmpty(dataInicio))
                   {
                   Query += " AND   PRONT.DT_INCLUSAO >= CONVERT(DATETIME, '" + dataInicio + " ',103) ";
                   }
                   if (!string.IsNullOrEmpty(dataFim))
                   {
                       Query += "   AND	CONVERT(DATE, PRONT.DT_INCLUSAO,103) <= CONVERT(DATE, '" + dataFim + "',103) ";
                   }
			       Query += "                 ) AS TOTAL_ATENDIMENTO, ";
			       Query += "                 (SELECT COUNT(DISTINCT ES.CO_ES) FROM TB_ES AS ES ";
			       Query += "                 INNER JOIN TB_US AS US ";
				   Query += "                     ON US.CO_US = ES.CO_US ";
			       Query += "                 INNER JOIN TB_UF AS UF ";
				   Query += "                     ON UF.CO_UF = US.CO_UF ";
			       Query += "                 INNER JOIN TB_CIDADE AS CID ";
				   Query += "                     ON CID.CO_UF = UF.CO_UF ";
                   Query += "                 WHERE 1 = 1 ";
                   if (intCodUf > 0)
                   {
                       Query += "                 AND US.CO_UF = " + intCodUf;
                   }
                   Query += " ";
			       Query += "                  ) AS N_EQUIPES_TOTAL ";		
	               Query += "          FROM TB_ES AS ES ";
	               Query += "          INNER JOIN TB_US AS US ";
                   Query += " ON US.CO_US = ES.CO_US ";
	               Query += "        INNER JOIN TB_UF AS UF ";
		           Query += "            ON UF.CO_UF = US.CO_UF ";
	               Query += "         INNER JOIN TB_CIDADE AS CID ";
		           Query += "             ON CID.CO_UF = UF.CO_UF ";
                   Query += "                 WHERE 1 = 1 ";
                   if (intCodUf > 0)
                   {
                       Query += "                 AND US.CO_UF = " + intCodUf;
                   }

           

            SqlCommand cmd = new SqlCommand(Query, _conexao);

            cmd.Connection = _conexao;

            if (_conexao.State == ConnectionState.Closed)
            {
                _conexao.Open();
            }
            SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
            dataAdapter.Fill(ds);

            _conexao.Close();

            return ds;
        }

        public DataSet ListarAtmEqAb(int intCodUf, string dataInicio, string dataFim)
        {
            DataSet ds = new DataSet();

            string Query = "SELECT DISTINCT TOP 1 ";
                   Query += "      (SELECT COUNT(*) FROM TB_PRONTUARIO AS PRONT ";  
			       Query += "                    INNER JOIN TB_USUARIO AS USU "; 
				   Query += "               ON USU.CO_USUARIO = PRONT.CO_USUARIO "; 
			       Query += "           INNER JOIN TB_ES AS ES ";
				   Query += "               ON ES.CO_ES = USU.CO_ES ";
			       Query += "           INNER JOIN TB_US AS US ";
				   Query += "               ON US.CO_US = ES.CO_US ";
                   Query += "                 WHERE 1 = 1 ";
                   if (intCodUf > 0)
                   {
                       Query += "                 AND US.CO_UF = " + intCodUf;
                   }

                   if (!string.IsNullOrEmpty(dataInicio))
                   {
                       Query += " AND   PRONT.DT_INCLUSAO >= CONVERT(DATE, '" + dataInicio + " ',103) ";
                   }
                   if (!string.IsNullOrEmpty(dataFim))
                   {
                       Query += "   AND	CONVERT(DATE, PRONT.DT_INCLUSAO,103) <= CONVERT(DATE, '" + dataFim + "',103) ";
                   }
			       Query += "        )   AS TOTAL_ATENDIMENTO, ";
			       Query += "           (SELECT COUNT(DISTINCT ES.CO_ES) FROM TB_ES AS ES ";
			       Query += "           INNER JOIN TB_US AS US ";
				   Query += "               ON US.CO_US = ES.CO_US ";
			       Query += "           INNER JOIN TB_UF AS UF ";
				   Query += "               ON UF.CO_UF = US.CO_UF ";
			       Query += "           INNER JOIN TB_CIDADE AS CID ";
				   Query += "               ON CID.CO_UF = UF.CO_UF ";
			       Query += "           INNER JOIN TB_ATENCAO_SUS AS AT ";
				   Query += "               ON AT.CO_ATENCAO_SUS = ES.CO_ATENCAO_SUS ";
                   Query += "                 WHERE 1 = 1 ";
                   if (intCodUf > 0)
                   {
                       Query += "                 AND US.CO_UF = " + intCodUf;
                   }
                   Query += " AND AT.CO_ATENCAO_SUS = 1) AS N_EQUIPES_AB ";	
	               Query += "   FROM TB_ES AS ES ";
	               Query += "   INNER JOIN TB_US AS US "; 
		           Query += "       ON US.CO_US = ES.CO_US ";
	               Query += "   INNER JOIN TB_UF AS UF ";
		           Query += "       ON UF.CO_UF = US.CO_UF ";
	               Query += "   INNER JOIN TB_CIDADE AS CID ";
		           Query += "       ON CID.CO_UF = UF.CO_UF ";
                   Query += "                WHERE 1 = 1 ";
            if (intCodUf > 0)
            {
                Query += "                 AND US.CO_UF = " + intCodUf;
            }



            SqlCommand cmd = new SqlCommand(Query, _conexao);

            cmd.Connection = _conexao;

            if (_conexao.State == ConnectionState.Closed)
            {
                _conexao.Open();
            }
            SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
            dataAdapter.Fill(ds);

            _conexao.Close();

            return ds;
        }

        public DataSet ListarAtmEqMac(int intCodUf, string dataInicio, string dataFim)
        {
            DataSet ds = new DataSet();

            string Query = "SELECT DISTINCT TOP 1 ";
                   Query += "   (SELECT COUNT(*) FROM TB_PRONTUARIO AS PRONT ";
			       Query += "                  INNER JOIN TB_USUARIO AS USU ";
				   Query += "                      ON USU.CO_USUARIO = PRONT.CO_USUARIO ";
			       Query += "                  INNER JOIN TB_ES AS ES ";
				   Query += "                      ON ES.CO_ES = USU.CO_ES ";
			       Query += "                  INNER JOIN TB_US AS US ";
				   Query += "                      ON US.CO_US = ES.CO_US ";
                   Query += "          WHERE 1 = 1 ";
                   if (intCodUf > 0)
                   {
                       Query += "                 AND US.CO_UF = " + intCodUf;
                   }
                   if (!string.IsNullOrEmpty(dataInicio))
                   {
                       Query += " AND   PRONT.DT_INCLUSAO >= CONVERT(DATETIME, '" + dataInicio + " ',103) ";
                   }
                   if (!string.IsNullOrEmpty(dataFim))
                   {
                       Query += "   AND	CONVERT(DATE, PRONT.DT_INCLUSAO,103) <= CONVERT(DATE, '" + dataFim + "',103) ";
                   }
                   Query += "                      ) AS TOTAL_ATENDIMENTO, ";
			       Query += "                  (SELECT COUNT(DISTINCT ES.CO_ES) FROM TB_ES AS ES ";
			       Query += "                  INNER JOIN TB_US AS US ";
				   Query += "                      ON US.CO_US = ES.CO_US ";
			       Query += "                  INNER JOIN TB_UF AS UF ";  
				   Query += "                      ON UF.CO_UF = US.CO_UF "; 
			       Query += "                  INNER JOIN TB_CIDADE AS CID ";
				   Query += "                      ON CID.CO_UF = UF.CO_UF ";
			       Query += "                   INNER JOIN TB_ATENCAO_SUS AS AT ";
				   Query += "                      ON AT.CO_ATENCAO_SUS = ES.CO_ATENCAO_SUS ";
                    Query += "          WHERE 1 = 1 ";
                    if (intCodUf > 0)
                    {
                        Query += "                 AND US.CO_UF = " + intCodUf;
                    }
                   Query += " AND (AT.CO_ATENCAO_SUS = 2 OR AT.CO_ATENCAO_SUS = 3)) AS N_EQUIPES_MAC ";			
	               Query += "          FROM TB_ES AS ES "; 
	               Query += "          INNER JOIN TB_US AS US ";
		           Query += "              ON US.CO_US = ES.CO_US ";
	               Query += "          INNER JOIN TB_UF AS UF ";
		           Query += "              ON UF.CO_UF = US.CO_UF ";
	               Query += "          INNER JOIN TB_CIDADE AS CID ";
		           Query += "              ON CID.CO_UF = UF.CO_UF ";
	               Query += "          WHERE 1 = 1 ";
                    if (intCodUf > 0)
                    {
                        Query += "                 AND US.CO_UF = " + intCodUf;
                    }



            SqlCommand cmd = new SqlCommand(Query, _conexao);

            cmd.Connection = _conexao;

            if (_conexao.State == ConnectionState.Closed)
            {
                _conexao.Open();
            }
            SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
            dataAdapter.Fill(ds);

            _conexao.Close();

            return ds;
        }

        public DataSet ExportExcel(int intCodUF)
        {
            DataSet ds = new DataSet();

            string Query = "SELECT DISTINCT * FROM TB_USUARIO AS USU";
	        Query += "    LEFT JOIN TB_ANAMNESE AS ANA";
		    Query += "        ON ANA.CO_ANAMNESE = (SELECT TOP 1 A.CO_ANAMNESE";
			Query += "						            FROM TB_ANAMNESE AS A";
			Query += "						            WHERE A.CO_USUARIO = USU.CO_USUARIO ORDER BY A.CO_ANAMNESE DESC)";
	        Query += "    LEFT JOIN TB_AVD AS AVD";
		    Query += "        ON AVD.CO_AVD = (SELECT TOP 1 V.CO_AVD";
			Query += "						            FROM TB_AVD AS V";
			Query += "						            WHERE V.CO_USUARIO = USU.CO_USUARIO ORDER BY V.CO_AVD DESC)";
	        Query += "    LEFT JOIN TB_CONTEXTO AS CONT";
		    Query += "        ON CONT.CO_CONTEXTO = (SELECT TOP 1 C.CO_CONTEXTO";
			Query += "						            FROM TB_CONTEXTO AS C";
			Query += "						            WHERE C.CO_USUARIO = USU.CO_USUARIO ORDER BY C.CO_CONTEXTO DESC)";
	        Query += "    LEFT JOIN TB_PRONTUARIO AS PRONT";
		    Query += "        ON PRONT.CO_PRONTUARIO = (SELECT TOP 1 T.CO_PRONTUARIO";
			Query += "						            FROM TB_PRONTUARIO AS T";
			Query += "						            WHERE T.CO_USUARIO = USU.CO_USUARIO ORDER BY T.CO_PRONTUARIO DESC)";
	        Query += "    INNER JOIN TB_ES AS ES";
		    Query += "        ON ES.CO_ES = USU.CO_ES";
	        Query += "    INNER JOIN TB_US AS US";
		    Query += "        ON US.CO_US = ES.CO_US";
	        Query += "    INNER JOIN TB_UF AS UF";
		    Query += "        ON UF.CO_UF = US.CO_UF";
            Query += "    WHERE UF.CO_UF = " + intCodUF;
	        Query += "    ORDER BY USU.NO_NOME";
            


            SqlCommand cmd = new SqlCommand(Query, _conexao);

            cmd.Connection = _conexao;

            if (_conexao.State == ConnectionState.Closed)
            {
                _conexao.Open();
            }
            SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
            dataAdapter.Fill(ds);

            _conexao.Close();

            return ds;
        }

        public DataSet AdesaoEnfermeiro(int intCodUF, string strDataInicial, string strDataFinal, string strFiltro)
        {
            DataSet ds = new DataSet();
            string strTabela = "";
            if(strFiltro == "0")
            {
                strTabela = "TB_PRESCRICAO_ENFERMEIRO";
            }

            if (strFiltro == "1")
            {
                strTabela = "TB_PRONTUARIO";
            }

            string Query = "SELECT COUNT(DISTINCT PRO.CO_PROFISSIONAL) AS QTD_ENFERMEIROS_ADERIDOS,";
            Query += "        (SELECT COUNT(DISTINCT PRO.CO_PROFISSIONAL) FROM " + strTabela + " AS PRONT";
	        Query += "            INNER JOIN TB_PROFISSIONAL AS PRO";
		    Query += "                ON PRO.CO_PROFISSIONAL = PRONT.CO_PROFISSIONAL";
	        Query += "            INNER JOIN TB_ALOCACAO_PROF AS ALOC";
		    Query += "                ON ALOC.CO_PROFISSICIONAL = PRO.CO_PROFISSIONAL";
	        Query += "            INNER JOIN TB_ES AS ES";
		    Query += "                ON ES.CO_ES = ALOC.CO_ES";
	        Query += "            INNER JOIN TB_US AS US";
		    Query += "                ON US.CO_US = ES.CO_US";
	        Query += "            INNER JOIN TB_US AS UF";
		    Query += "                ON UF.CO_UF = US.CO_UF";
	        Query += "            WHERE PRO.CO_CATEGORIA_PROF = 2";
            Query += "            AND UF.CO_UF = " + intCodUF + ") AS QTD_ENFERMEIROS";
            Query += "         FROM " + strTabela + " AS PRONT";
	        Query += "            INNER JOIN TB_PROFISSIONAL AS PRO";
		    Query += "                ON PRO.CO_PROFISSIONAL = PRONT.CO_PROFISSIONAL";
	        Query += "            INNER JOIN TB_ALOCACAO_PROF AS ALOC";
		    Query += "                ON ALOC.CO_PROFISSICIONAL = PRO.CO_PROFISSIONAL";
	        Query += "            INNER JOIN TB_ES AS ES";
		    Query += "                ON ES.CO_ES = ALOC.CO_ES";
	        Query += "            INNER JOIN TB_US AS US";
		    Query += "                ON US.CO_US = ES.CO_US";
	        Query += "            INNER JOIN TB_US AS UF";
		    Query += "                ON UF.CO_UF = US.CO_UF";
	        Query += "            WHERE PRO.CO_CATEGORIA_PROF = 2";
	        Query += "            AND UF.CO_UF = " + intCodUF;
            
            
            if (!string.IsNullOrEmpty(strDataInicial))
            {
                Query += "            AND CONVERT(DATE,PRONT.DT_INCLUSAO,103) >= CONVERT(DATE,'" + strDataInicial + "',103)";
            }

            if (!string.IsNullOrEmpty(strDataFinal))
            {
                Query += "            AND CONVERT(DATE,PRONT.DT_INCLUSAO,103) <= CONVERT(DATE,'" + strDataFinal + "',103)";
            }
            
            SqlCommand cmd = new SqlCommand(Query, _conexao);

            cmd.Connection = _conexao;

            if (_conexao.State == ConnectionState.Closed)
            {
                _conexao.Open();
            }
            SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
            dataAdapter.Fill(ds);

            _conexao.Close();

            return ds;
        }
    }
}
